﻿using NLog;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using ZoneLine.Common;
using ZoneLine.Views;


namespace Manager.Common
{
    public class NPOIService
    {
        #region 将泛型集合中的实体导出到指定的Excel文件

        /// <summary>
        /// 将泛型集合中的实体导出到指定的Excel文件
        /// </summary>
        /// <typeparam name="T">泛型方法中的类型</typeparam>
        /// <param name="fileName">Excel路径和文件名</param>
        /// <param name="dataList">包含若干对象的泛型集合</param>
        /// <param name="columnNames">实体列的中文标题集合</param>
        /// <param name="titletext">标题名称</param>
        /// <param name="version">Excel的版本号，规定0:2007以前版本，否则为2007及以上版本</param>
        /// <returns>成功返回ture</returns>
        public static bool ExportToExcel<T>(string fileName, List<T> dataList, string titletext = "报表", int version = 1) where T : class
        {
            //【1】基于NPOI创建工作簿和工作表对象
            HSSFWorkbook hssf = new HSSFWorkbook();   //2007以下版本
            XSSFWorkbook xssf = new XSSFWorkbook();   //2007以上版本
            //根据不同的office版本创建不同的工作簿对象
            IWorkbook workBook = null;
            if (version == 0)
                workBook = hssf;
            else
                workBook = xssf;

            //【2】创建工作表
            ISheet sheet = workBook.CreateSheet("sheet1");//请根据实际需要把工作表的名称，变成一个参数

            var modelTemp = dataList.First();

            Type type = modelTemp.GetType();
            PropertyInfo[] propertyinfos = type.GetProperties().OrderBy(o=>o.GetCustomAttribute<DgColumnAttr>()?.Index ?? int.MaxValue).ToArray();//获取类型的公共属性
            var columnNames = new Dictionary<string, string>();

            var ignoreDic = new Dictionary<int, PropertyInfo>();
            int index = 0;
            foreach (var property in propertyinfos)
            {
                var ignore = property.GetCustomAttribute(typeof(IgnoreAttribute));
                if (ignore != null)
                {
                    ignoreDic.Add(index, property);
                    index++;
                    continue;
                }

                var rlt = property.GetCustomAttribute(typeof(DgColumnAttr));
                if (rlt != null)
                {
                    var temp = rlt as DgColumnAttr;
                    if (temp != null)
                    {
                        columnNames.Add(property.Name, temp.HeaderName);
                    }
                }
                else
                {
                    columnNames.Add(property.Name, property.Name);
                }
                index++;
            }

            //创建合并单元格
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, propertyinfos.Length - 1 - ignoreDic.Count);
            IRow rowTitle1 = sheet.CreateRow(0);
            for (int i = 0; i < propertyinfos.Length - 1 - ignoreDic.Count; i++)
            {
                ICell cell = rowTitle1.CreateCell(i);
            }
            sheet.AddMergedRegion(region);


            ICell titleCell = rowTitle1.CreateCell(0);   //创建单元格  propertyinfos[i].Name
            titleCell.SetCellValue(titletext);// 设置行标题
            SetCellStyle(workBook, titleCell);

            //【3】循环生成列标题和设置样式
            IRow rowTitle = sheet.CreateRow(1);
            for (int i = 0; i < propertyinfos.Length; i++)
            {
                if (ignoreDic.ContainsKey(i))
                {
                    continue;
                }
                ICell cell = rowTitle.CreateCell(i);   //创建单元格  propertyinfos[i].Name
                cell.SetCellValue(columnNames[propertyinfos[i].Name]);// 设置行标题
                SetCellStyle(workBook, cell);                                                 //设置单元格边框 
                SetColumnWidth(sheet, i, 35);                                                //设置列宽   
            }
            SetCellStyle(workBook);
            //【4】循环实体集合生成各行数据
            try
            {
                for (int i = 1; i < dataList.Count + 1; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < propertyinfos.Length; j++)
                    {
                        if (ignoreDic.ContainsKey(j))
                        {
                            continue;
                        }
                        ICell cell = row.CreateCell(j);
                        T model = dataList[i - 1];    //根据泛型找到具体化的实体对象
                        string value = propertyinfos[j].GetValue(model, null)?.ToString();//基于反射获取实体属性值
                        SetCellStyle(workBook, cell);
                        cell.SetCellValue(value);  //赋值
                    }
                }
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.ToString());
            }
           
            //【5】保存为Excel文件
            using (FileStream fs = new FileStream(fileName, FileMode.Create))
            {
                workBook.Write(fs);
                return true;
            }
        }
        #endregion

        #region 辅助方法
        static ICellStyle style = null;
        /// <summary>
        /// 设置cell单元格边框的公共方法
        /// </summary>
        /// <param name="workBook">接口类型的工作簿，能适应不同版本</param>
        /// <param name="cell">cell单元格对象</param>
        private static void SetCellStyle(IWorkbook workBook, ICell cell)
        {
            if (style == null)
            {
                style = workBook.CreateCellStyle();

                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.Alignment = HorizontalAlignment.Center;//水平对齐
                style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐
                                                                   //设置字体
                IFont font = workBook.CreateFont();
                font.FontName = "微软雅黑";
                font.FontHeight = 15 * 15;
                font.Color = IndexedColors.Green.Index;   //字体颜色         
                                                          //font.Color =(short )FontColor .Red  ;
                style.SetFont(font);
            }
           
            cell.CellStyle = style;
        }
        private static void SetCellStyle(IWorkbook workBook)
        {
            ICellStyle style = workBook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;

            style.BorderTop = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;//水平对齐
            style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐
            //设置字体
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeight = 15 * 15;
            font.Color = IndexedColors.Green.Index;   //字体颜色         
            //font.Color =(short )FontColor .Red  ;
            style.SetFont(font);

        }
        /// <summary>
        /// 设置cell单元格列宽的公共方法
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="index">第几列</param>
        /// <param name="width">具体宽度值</param>
        private static void SetColumnWidth(ISheet sheet, int index, int width)
        {
            sheet.SetColumnWidth(index, width * 256);
        }

        #endregion
    }
}
